from fastapi import APIRouter, Depends, HTTPException, Query, UploadFile, File
from typing import List, Optional
import os
import uuid
import shutil
from datetime import datetime
from PIL import Image

from pydantic import BaseModel

from models.database import get_db_connection
from models.schemas import (
    UserCreate, UserLogin, UserUpdate, UserResponse,
    FavoriteCreate, FavoriteResponse, PasswordResetRequest,
    ForgotPasswordRequest, ForgotPasswordResponse, MessageResponse,
    SuccessResponse, Token, PaginatedFavoriteResponse, AddressCreate, AddressUpdate
)
from utils.auth import get_password_hash, verify_password, create_access_token, get_current_user
import mysql.connector

router = APIRouter(prefix="/users", tags=["用户管理"])

# 头像相关配置
AVATAR_BASE_DIR = "static/avatars"
DEFAULT_AVATAR = "/static/avatars/default/default.png"
ALLOWED_IMAGE_TYPES = ["image/jpeg", "image/png", "image/webp"]
MAX_FILE_SIZE = 5 * 1024 * 1024  # 5MB


# 地址管理相关的Pydantic模型
@router.post("/register", response_model=SuccessResponse)
async def register(user: UserCreate):
    """用户注册"""
    print(f"🔍 注册请求: 用户名='{user.user_name}', 手机号='{user.user_phone}', 地址='{user.address}'")

    connection = get_db_connection()
    if not connection:
        raise HTTPException(status_code=500, detail="数据库连接失败")

    cursor = None
    try:
        cursor = connection.cursor(dictionary=True)

        # 第一步：严格检查用户名是否存在
        cursor.execute("SELECT user_id FROM user WHERE user_name = %s", (user.user_name,))
        existing_user = cursor.fetchone()
        if existing_user:
            print(f"❌ 用户名已存在: {user.user_name}")
            raise HTTPException(status_code=400, detail="用户名已存在")

        # 第二步：严格检查手机号是否存在
        cursor.execute("SELECT user_id FROM user WHERE user_phone = %s", (user.user_phone,))
        existing_phone = cursor.fetchone()
        if existing_phone:
            print(f"❌ 手机号已被注册: {user.user_phone}")
            raise HTTPException(status_code=400, detail="手机号已被注册")

        # 第三步：加密密码
        hashed_password = get_password_hash(user.user_password)

        # 第四步：创建用户（包含地址字段和默认头像）
        print(f"✅ 准备创建用户: {user.user_name}, {user.user_phone}, {user.address}")
        cursor.execute(
            "INSERT INTO user (user_name, user_password, user_phone, address, avatar_url) VALUES (%s, %s, %s, %s, %s)",
            (user.user_name, hashed_password, user.user_phone, user.address, DEFAULT_AVATAR)
        )
        connection.commit()
        print("✅ 用户注册成功")

        return SuccessResponse(
            success=True,
            message="用户注册成功",
            data={"user_name": user.user_name}
        )

    except HTTPException:
        # 直接重新抛出HTTP异常
        raise
    except mysql.connector.Error as e:
        connection.rollback()
        print(f"❌ 数据库错误: {e}")

        # 强制处理：如果是任何数据库错误，都返回400
        # 这样可以避免500错误，让前端能够正确显示错误信息
        if "user_phone" in str(e) or "unique_user_phone" in str(e) or "1062" in str(e):
            raise HTTPException(status_code=400, detail="手机号已被注册")
        elif "user_name" in str(e):
            raise HTTPException(status_code=400, detail="用户名已存在")
        else:
            raise HTTPException(status_code=400, detail="注册失败，请检查信息是否正确")
    except Exception as e:
        connection.rollback()
        print(f"❌ 其他异常: {e}")
        # 所有其他异常也返回400，避免500
        raise HTTPException(status_code=400, detail="注册失败，请重试")
    finally:
        if cursor:
            cursor.close()
        if connection:
            connection.close()


@router.post("/login", response_model=Token)
async def login(user: UserLogin):
    """用户登录"""
    connection = get_db_connection()
    if not connection:
        raise HTTPException(status_code=500, detail="数据库连接失败")

    try:
        cursor = connection.cursor(dictionary=True)
        cursor.execute(
            "SELECT user_id, user_name, user_password FROM user WHERE user_name = %s",
            (user.user_name,)
        )
        user_data = cursor.fetchone()

        if not user_data or not verify_password(user.user_password, user_data['user_password']):
            raise HTTPException(status_code=401, detail="用户名或密码错误")

        # 生成JWT令牌
        access_token = create_access_token(
            data={"user_id": user_data['user_id'], "user_name": user_data['user_name']}
        )

        return Token(
            access_token=access_token,
            token_type="bearer",
            user_id=user_data['user_id'],
            user_name=user_data['user_name']
        )

    except mysql.connector.Error as e:
        raise HTTPException(status_code=500, detail=f"数据库错误: {e}")
    finally:
        cursor.close()
        connection.close()


@router.get("/profile", response_model=UserResponse)
async def get_profile(current_user: dict = Depends(get_current_user)):
    """获取当前用户信息（包含地址和头像字段）"""
    connection = get_db_connection()
    if not connection:
        raise HTTPException(status_code=500, detail="数据库连接失败")

    try:
        cursor = connection.cursor(dictionary=True)
        cursor.execute("""
            SELECT user_id, user_name, user_phone, address, avatar_url, created_at, updated_at
            FROM user 
            WHERE user_id = %s
        """, (current_user['user_id'],))

        user_data = cursor.fetchone()
        if not user_data:
            raise HTTPException(status_code=404, detail="用户不存在")

        return user_data

    except mysql.connector.Error as e:
        raise HTTPException(status_code=500, detail=f"数据库错误: {e}")
    finally:
        cursor.close()
        connection.close()


@router.put("/profile", response_model=SuccessResponse)
async def update_profile(update_data: UserUpdate, current_user: dict = Depends(get_current_user)):
    """更新用户信息（包含地址字段）"""
    connection = get_db_connection()
    if not connection:
        raise HTTPException(status_code=500, detail="数据库连接失败")

    try:
        cursor = connection.cursor()

        update_fields = []
        update_values = []

        if update_data.user_name:
            # 检查新用户名是否已被其他用户使用
            cursor.execute("SELECT user_id FROM user WHERE user_name = %s AND user_id != %s",
                           (update_data.user_name, current_user['user_id']))
            if cursor.fetchone():
                raise HTTPException(status_code=400, detail="用户名已被使用")
            update_fields.append("user_name = %s")
            update_values.append(update_data.user_name)

        if update_data.user_password:
            hashed_password = get_password_hash(update_data.user_password)
            update_fields.append("user_password = %s")
            update_values.append(hashed_password)

        if update_data.user_phone:
            update_fields.append("user_phone = %s")
            update_values.append(update_data.user_phone)

        if update_data.address is not None:  # 允许空地址
            update_fields.append("address = %s")
            update_values.append(update_data.address)

        if not update_fields:
            raise HTTPException(status_code=400, detail="没有要更新的字段")

        # 添加更新时间戳
        update_fields.append("updated_at = CURRENT_TIMESTAMP")

        update_values.append(current_user['user_id'])
        query = f"UPDATE user SET {', '.join(update_fields)} WHERE user_id = %s"

        cursor.execute(query, update_values)
        connection.commit()

        return SuccessResponse(
            success=True,
            message="用户信息更新成功"
        )

    except mysql.connector.Error as e:
        connection.rollback()
        raise HTTPException(status_code=500, detail=f"数据库错误: {e}")
    finally:
        cursor.close()
        connection.close()


# 头像管理相关接口
@router.post("/avatar", response_model=SuccessResponse)
async def upload_avatar(
        file: UploadFile = File(...),
        current_user: dict = Depends(get_current_user)
):
    """上传用户头像"""
    # 验证文件类型
    if file.content_type not in ALLOWED_IMAGE_TYPES:
        raise HTTPException(status_code=400, detail="只支持 JPEG、PNG、WebP 格式的图片")

    # 验证文件大小
    file.file.seek(0, 2)  # 移动到文件末尾
    file_size = file.file.tell()
    file.file.seek(0)  # 重置文件指针
    if file_size > MAX_FILE_SIZE:
        raise HTTPException(status_code=400, detail="文件大小不能超过 5MB")

    connection = get_db_connection()
    if not connection:
        raise HTTPException(status_code=500, detail="数据库连接失败")

    try:
        cursor = connection.cursor(dictionary=True)

        # 获取用户当前头像信息
        cursor.execute("SELECT avatar_url FROM user WHERE user_id = %s", (current_user['user_id'],))
        user_data = cursor.fetchone()
        if not user_data:
            raise HTTPException(status_code=404, detail="用户不存在")

        old_avatar_path = user_data['avatar_url']

        # 创建用户专属目录
        user_avatar_dir = os.path.join(AVATAR_BASE_DIR, str(current_user['user_id']))
        os.makedirs(user_avatar_dir, exist_ok=True)

        # 生成唯一文件名
        file_extension = "webp"
        timestamp = datetime.now().strftime("%Y%m%d%H%M%S")
        unique_filename = f"{uuid.uuid4().hex}_{timestamp}.{file_extension}"
        avatar_filepath = os.path.join(user_avatar_dir, unique_filename)

        # 处理并保存图片
        try:
            # 打开图片
            image = Image.open(file.file)

            # 转换为 RGB 模式（处理 PNG 透明背景）
            if image.mode in ('RGBA', 'LA'):
                background = Image.new('RGB', image.size, (255, 255, 255))
                background.paste(image, mask=image.split()[-1])
                image = background

            # 调整图片尺寸（最大 500x500）
            image.thumbnail((500, 500), Image.Resampling.LANCZOS)

            # 保存为 webp 格式
            image.save(avatar_filepath, "WEBP", quality=85)

        except Exception as e:
            raise HTTPException(status_code=400, detail=f"图片处理失败: {str(e)}")

        # 构建相对路径用于数据库存储
        avatar_relative_path = f"/static/avatars/{current_user['user_id']}/{unique_filename}"

        # 更新数据库
        cursor.execute(
            "UPDATE user SET avatar_url = %s, updated_at = CURRENT_TIMESTAMP WHERE user_id = %s",
            (avatar_relative_path, current_user['user_id'])
        )
        connection.commit()

        # 删除旧头像文件（如果不是默认头像且不是default.webp）
        if (old_avatar_path and
                old_avatar_path != DEFAULT_AVATAR and
                not old_avatar_path.endswith('default.webp')):
            try:
                old_avatar_abs_path = old_avatar_path.lstrip('/')
                if os.path.exists(old_avatar_abs_path):
                    os.remove(old_avatar_abs_path)
            except Exception as e:
                print(f"⚠️ 删除旧头像失败: {e}")

        return SuccessResponse(
            success=True,
            message="头像上传成功",
            data={"avatar_url": avatar_relative_path}
        )

    except mysql.connector.Error as e:
        connection.rollback()
        raise HTTPException(status_code=500, detail=f"数据库错误: {e}")
    except Exception as e:
        connection.rollback()
        raise HTTPException(status_code=500, detail=f"头像上传失败: {str(e)}")
    finally:
        cursor.close()
        connection.close()


@router.get("/avatar", response_model=dict)
async def get_avatar(current_user: dict = Depends(get_current_user)):
    """获取用户头像信息"""
    connection = get_db_connection()
    if not connection:
        raise HTTPException(status_code=500, detail="数据库连接失败")

    try:
        cursor = connection.cursor(dictionary=True)
        cursor.execute("SELECT avatar_url FROM user WHERE user_id = %s", (current_user['user_id'],))
        user_data = cursor.fetchone()

        if not user_data:
            raise HTTPException(status_code=404, detail="用户不存在")

        avatar_url = user_data['avatar_url'] or DEFAULT_AVATAR

        return {
            "avatar_url": avatar_url,
            "is_default": avatar_url == DEFAULT_AVATAR
        }

    except mysql.connector.Error as e:
        raise HTTPException(status_code=500, detail=f"数据库错误: {e}")
    finally:
        cursor.close()
        connection.close()


@router.delete("/avatar", response_model=SuccessResponse)
async def delete_avatar(current_user: dict = Depends(get_current_user)):
    """删除用户头像（恢复默认头像）"""
    connection = get_db_connection()
    if not connection:
        raise HTTPException(status_code=500, detail="数据库连接失败")

    try:
        cursor = connection.cursor(dictionary=True)

        # 获取用户当前头像信息
        cursor.execute("SELECT avatar_url FROM user WHERE user_id = %s", (current_user['user_id'],))
        user_data = cursor.fetchone()
        if not user_data:
            raise HTTPException(status_code=404, detail="用户不存在")

        old_avatar_path = user_data['avatar_url']

        # 更新数据库为默认头像
        cursor.execute(
            "UPDATE user SET avatar_url = %s, updated_at = CURRENT_TIMESTAMP WHERE user_id = %s",
            (DEFAULT_AVATAR, current_user['user_id'])
        )
        connection.commit()

        # 删除旧头像文件（如果不是默认头像）
        if old_avatar_path and old_avatar_path != DEFAULT_AVATAR:
            try:
                old_avatar_abs_path = old_avatar_path.lstrip('/')
                if os.path.exists(old_avatar_abs_path):
                    os.remove(old_avatar_abs_path)

                # 尝试删除用户头像目录（如果为空）
                user_avatar_dir = os.path.join(AVATAR_BASE_DIR, str(current_user['user_id']))
                if os.path.exists(user_avatar_dir) and not os.listdir(user_avatar_dir):
                    os.rmdir(user_avatar_dir)
            except Exception as e:
                print(f"⚠️ 清理旧头像文件失败: {e}")

        return SuccessResponse(
            success=True,
            message="头像已删除，恢复默认头像",
            data={"avatar_url": DEFAULT_AVATAR}
        )

    except mysql.connector.Error as e:
        connection.rollback()
        raise HTTPException(status_code=500, detail=f"数据库错误: {e}")
    finally:
        cursor.close()
        connection.close()


# 地址管理相关接口
# 地址管理相关接口
@router.post("/address", response_model=SuccessResponse)
async def add_address(address_data: AddressCreate, current_user: dict = Depends(get_current_user)):
    """添加收货地址"""
    # 验证手机号格式
    if not address_data.contact_phone.isdigit() or len(address_data.contact_phone) != 11:
        raise HTTPException(status_code=400, detail="手机号格式不正确")

    # 拼接地址信息：联系人，手机号，收货地址
    formatted_address = f"{address_data.contact_name}，{address_data.contact_phone}，{address_data.address_detail}"

    connection = get_db_connection()
    if not connection:
        raise HTTPException(status_code=500, detail="数据库连接失败")

    try:
        cursor = connection.cursor()

        # 检查是否设置为默认地址，如果是，需要先取消其他默认地址
        if address_data.is_default:
            cursor.execute(
                "UPDATE user_address SET is_default = 0, updated_at = CURRENT_TIMESTAMP WHERE user_id = %s",
                (current_user['user_id'],)
            )

        # 插入新地址到 user_address 表
        cursor.execute(
            """INSERT INTO user_address (user_id, address, is_default, created_at, updated_at) 
               VALUES (%s, %s, %s, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)""",
            (current_user['user_id'], formatted_address, address_data.is_default)
        )
        connection.commit()

        # 获取新插入的地址ID
        address_id = cursor.lastrowid

        return SuccessResponse(
            success=True,
            message="收货地址添加成功",
            data={
                "address_id": address_id,
                "formatted_address": formatted_address,
                "contact_name": address_data.contact_name,
                "contact_phone": address_data.contact_phone,
                "address_detail": address_data.address_detail,
                "is_default": address_data.is_default
            }
        )

    except mysql.connector.Error as e:
        connection.rollback()
        raise HTTPException(status_code=500, detail=f"数据库错误: {e}")
    finally:
        cursor.close()
        connection.close()


@router.put("/address/{address_id}", response_model=SuccessResponse)
async def update_address(address_id: int, address_data: AddressUpdate, current_user: dict = Depends(get_current_user)):
    """更新收货地址"""
    # 验证手机号格式
    if not address_data.contact_phone.isdigit() or len(address_data.contact_phone) != 11:
        raise HTTPException(status_code=400, detail="手机号格式不正确")

    # 拼接地址信息：联系人，手机号，收货地址
    formatted_address = f"{address_data.contact_name}，{address_data.contact_phone}，{address_data.address_detail}"

    connection = get_db_connection()
    if not connection:
        raise HTTPException(status_code=500, detail="数据库连接失败")

    try:
        cursor = connection.cursor(dictionary=True)

        # 检查地址是否存在且属于当前用户
        cursor.execute(
            "SELECT address_id FROM user_address WHERE address_id = %s AND user_id = %s",
            (address_id, current_user['user_id'])
        )
        address = cursor.fetchone()

        if not address:
            raise HTTPException(status_code=404, detail="地址不存在或无权访问")

        # 如果设置为默认地址，需要先取消其他默认地址
        if address_data.is_default:
            cursor.execute(
                "UPDATE user_address SET is_default = 0, updated_at = CURRENT_TIMESTAMP WHERE user_id = %s",
                (current_user['user_id'],)
            )

        # 更新地址信息
        cursor.execute(
            """UPDATE user_address 
               SET address = %s, is_default = %s, updated_at = CURRENT_TIMESTAMP 
               WHERE address_id = %s AND user_id = %s""",
            (formatted_address, address_data.is_default, address_id, current_user['user_id'])
        )
        connection.commit()

        return SuccessResponse(
            success=True,
            message="收货地址更新成功",
            data={
                "address_id": address_id,
                "formatted_address": formatted_address,
                "contact_name": address_data.contact_name,
                "contact_phone": address_data.contact_phone,
                "address_detail": address_data.address_detail,
                "is_default": address_data.is_default
            }
        )

    except mysql.connector.Error as e:
        connection.rollback()
        raise HTTPException(status_code=500, detail=f"数据库错误: {e}")
    finally:
        cursor.close()
        connection.close()


@router.delete("/address/{address_id}", response_model=SuccessResponse)
async def delete_address(address_id: int, current_user: dict = Depends(get_current_user)):
    """删除收货地址"""
    connection = get_db_connection()
    if not connection:
        raise HTTPException(status_code=500, detail="数据库连接失败")

    try:
        cursor = connection.cursor(dictionary=True)

        # 检查地址是否存在且属于当前用户
        cursor.execute(
            "SELECT address_id, is_default FROM user_address WHERE address_id = %s AND user_id = %s",
            (address_id, current_user['user_id'])
        )
        address = cursor.fetchone()

        if not address:
            raise HTTPException(status_code=404, detail="地址不存在或无权访问")

        # 删除地址
        cursor.execute(
            "DELETE FROM user_address WHERE address_id = %s AND user_id = %s",
            (address_id, current_user['user_id'])
        )
        connection.commit()

        # 如果删除的是默认地址，且还有其他地址，则设置第一个地址为默认地址
        if address['is_default']:
            cursor.execute(
                "SELECT address_id FROM user_address WHERE user_id = %s ORDER BY created_at ASC LIMIT 1",
                (current_user['user_id'],)
            )
            remaining_address = cursor.fetchone()

            if remaining_address:
                cursor.execute(
                    "UPDATE user_address SET is_default = 1 WHERE address_id = %s",
                    (remaining_address['address_id'],)
                )
                connection.commit()

        return SuccessResponse(
            success=True,
            message="收货地址删除成功",
            data={"address_id": address_id}
        )

    except mysql.connector.Error as e:
        connection.rollback()
        raise HTTPException(status_code=500, detail=f"数据库错误: {e}")
    finally:
        cursor.close()
        connection.close()


@router.get("/addresses", response_model=dict)
async def get_all_addresses(current_user: dict = Depends(get_current_user)):
    """获取当前用户的所有收货地址"""
    connection = get_db_connection()
    if not connection:
        raise HTTPException(status_code=500, detail="数据库连接失败")

    try:
        cursor = connection.cursor(dictionary=True)
        # 查询当前用户的所有地址，按默认地址优先、创建时间倒序排列
        cursor.execute("""
            SELECT address_id, user_id, address, is_default, created_at, updated_at 
            FROM user_address 
            WHERE user_id = %s 
            ORDER BY is_default DESC, created_at DESC
        """, (current_user['user_id'],))

        addresses_data = cursor.fetchall()

        if not addresses_data:
            return {
                "has_addresses": False,
                "message": "暂无收货地址",
                "addresses": [],
                "default_address_id": None
            }

        addresses = []
        default_address_id = None

        for address_item in addresses_data:
            # 解析地址格式：联系人，手机号，收货地址
            address_parts = address_item['address'].split('，')

            if len(address_parts) == 3:
                parsed_address = {
                    "address_id": address_item['address_id'],
                    "contact_name": address_parts[0],
                    "contact_phone": address_parts[1],
                    "address_detail": address_parts[2],
                    "is_default": bool(address_item['is_default']),
                    "formatted_address": address_item['address'],
                    "created_at": address_item['created_at'],
                    "updated_at": address_item['updated_at']
                }
            else:
                # 如果格式不正确，返回原始地址
                parsed_address = {
                    "address_id": address_item['address_id'],
                    "contact_name": "",
                    "contact_phone": "",
                    "address_detail": address_item['address'],
                    "is_default": bool(address_item['is_default']),
                    "formatted_address": address_item['address'],
                    "created_at": address_item['created_at'],
                    "updated_at": address_item['updated_at']
                }

            addresses.append(parsed_address)

            # 记录默认地址ID
            if address_item['is_default'] == 1:
                default_address_id = address_item['address_id']

        return {
            "has_addresses": True,
            "address_count": len(addresses),
            "default_address_id": default_address_id,
            "addresses": addresses
        }

    except mysql.connector.Error as e:
        raise HTTPException(status_code=500, detail=f"数据库错误: {e}")
    finally:
        cursor.close()
        connection.close()


@router.put("/addresses/{address_id}/set-default", response_model=SuccessResponse)
async def set_default_address(address_id: int, current_user: dict = Depends(get_current_user)):
    """设置默认地址"""
    connection = get_db_connection()
    if not connection:
        raise HTTPException(status_code=500, detail="数据库连接失败")

    try:
        cursor = connection.cursor(dictionary=True)

        # 检查地址是否存在且属于当前用户
        cursor.execute("""
            SELECT address_id, user_id 
            FROM user_address 
            WHERE address_id = %s AND user_id = %s
        """, (address_id, current_user['user_id']))

        address = cursor.fetchone()
        if not address:
            raise HTTPException(status_code=404, detail="地址不存在或无权访问")

        # 开始事务
        connection.start_transaction()

        # 先将该用户的所有地址的 is_default 设置为 0
        cursor.execute("""
            UPDATE user_address 
            SET is_default = 0, updated_at = CURRENT_TIMESTAMP 
            WHERE user_id = %s
        """, (current_user['user_id'],))

        # 然后将指定地址的 is_default 设置为 1
        cursor.execute("""
            UPDATE user_address 
            SET is_default = 1, updated_at = CURRENT_TIMESTAMP 
            WHERE address_id = %s AND user_id = %s
        """, (address_id, current_user['user_id']))

        connection.commit()

        return SuccessResponse(
            success=True,
            message="默认地址设置成功",
            data={
                "address_id": address_id,
                "user_id": current_user['user_id']
            }
        )

    except mysql.connector.Error as e:
        connection.rollback()
        raise HTTPException(status_code=500, detail=f"数据库错误: {e}")
    finally:
        cursor.close()
        connection.close()


@router.get("/address", response_model=dict)
async def get_address(current_user: dict = Depends(get_current_user)):
    """获取收货地址详情（解析后的格式）"""
    connection = get_db_connection()
    if not connection:
        raise HTTPException(status_code=500, detail="数据库连接失败")

    try:
        cursor = connection.cursor(dictionary=True)
        cursor.execute("SELECT address FROM user WHERE user_id = %s", (current_user['user_id'],))
        user_data = cursor.fetchone()

        if not user_data or not user_data['address']:
            return {
                "has_address": False,
                "message": "暂无收货地址"
            }

        # 解析地址格式：联系人，手机号，收货地址
        address_parts = user_data['address'].split('，')
        if len(address_parts) == 3:
            return {
                "has_address": True,
                "formatted_address": user_data['address'],
                "contact_name": address_parts[0],
                "contact_phone": address_parts[1],
                "address_detail": address_parts[2]
            }
        else:
            # 如果格式不正确，返回原始地址
            return {
                "has_address": True,
                "formatted_address": user_data['address'],
                "contact_name": "",
                "contact_phone": "",
                "address_detail": user_data['address']
            }

    except mysql.connector.Error as e:
        raise HTTPException(status_code=500, detail=f"数据库错误: {e}")
    finally:
        cursor.close()
        connection.close()


@router.post("/reset-password", response_model=SuccessResponse)
async def reset_password(
        reset_data: PasswordResetRequest,
        current_user: dict = Depends(get_current_user)
):
    """重置密码（需要验证原密码）"""
    connection = get_db_connection()
    if not connection:
        raise HTTPException(status_code=500, detail="数据库连接失败")

    try:
        cursor = connection.cursor(dictionary=True)

        # 验证原密码
        cursor.execute(
            "SELECT user_password FROM user WHERE user_id = %s",
            (current_user['user_id'],)
        )
        user_data = cursor.fetchone()

        if not user_data:
            raise HTTPException(status_code=404, detail="用户不存在")

        if not verify_password(reset_data.old_password, user_data['user_password']):
            raise HTTPException(status_code=401, detail="原密码错误")

        # 检查新密码是否与原密码相同
        if verify_password(reset_data.new_password, user_data['user_password']):
            raise HTTPException(status_code=400, detail="新密码不能与原密码相同")

        # 更新密码
        hashed_new_password = get_password_hash(reset_data.new_password)
        cursor.execute(
            "UPDATE user SET user_password = %s, updated_at = CURRENT_TIMESTAMP WHERE user_id = %s",
            (hashed_new_password, current_user['user_id'])
        )
        connection.commit()

        return SuccessResponse(
            success=True,
            message="密码重置成功",
            data={
                "user_id": current_user['user_id'],
                "user_name": current_user['user_name']
            }
        )

    except mysql.connector.Error as e:
        connection.rollback()
        raise HTTPException(status_code=500, detail=f"数据库错误: {e}")
    finally:
        cursor.close()
        connection.close()


@router.post("/forgot-password", response_model=SuccessResponse)
async def forgot_password(request_data: ForgotPasswordRequest):
    """忘记密码（通过手机号验证重置）"""
    connection = get_db_connection()
    if not connection:
        raise HTTPException(status_code=500, detail="数据库连接失败")

    cursor = None
    try:
        cursor = connection.cursor(dictionary=True)

        # 验证用户信息
        cursor.execute("""
            SELECT user_id, user_name, user_phone 
            FROM user 
            WHERE user_name = %s AND user_phone = %s
        """, (request_data.user_name, request_data.user_phone))

        user_data = cursor.fetchone()

        if not user_data:
            raise HTTPException(status_code=404, detail="用户信息不匹配，请检查用户名和手机号")

        # 加密新密码
        hashed_new_password = get_password_hash(request_data.new_password)

        # 更新密码
        cursor.execute(
            "UPDATE user SET user_password = %s, updated_at = CURRENT_TIMESTAMP WHERE user_id = %s",
            (hashed_new_password, user_data['user_id'])
        )
        connection.commit()

        return SuccessResponse(
            success=True,
            message="密码重置成功",
            data={
                "user_id": user_data['user_id'],
                "user_name": user_data['user_name']
            }
        )

    except mysql.connector.Error as e:
        connection.rollback()
        raise HTTPException(status_code=500, detail=f"数据库错误: {e}")
    finally:
        if cursor:
            cursor.close()
        if connection:
            connection.close()


@router.get("/favorites", response_model=PaginatedFavoriteResponse)
async def get_favorites(
        current_user: dict = Depends(get_current_user),
        page: int = Query(1, ge=1, description="页码"),
        page_size: int = Query(5, ge=1, le=50, description="每页数量")
):
    """获取用户收藏列表（分页）"""
    connection = get_db_connection()
    if not connection:
        raise HTTPException(status_code=500, detail="数据库连接失败")

    try:
        cursor = connection.cursor(dictionary=True)

        # 计算偏移量
        offset = (page - 1) * page_size

        # 查询收藏数据（分页）
        cursor.execute("""
            SELECT uf.user_id, uf.good_id, uf.good_name, uf.created_at,
                   g.good_sell_price, g.good_description, g.good_positive_review_rate
            FROM user_favorites uf
            JOIN goods g ON uf.good_id = g.good_id
            WHERE uf.user_id = %s
            ORDER BY uf.created_at DESC
            LIMIT %s OFFSET %s
        """, (current_user['user_id'], page_size, offset))

        favorites = cursor.fetchall()

        # 查询总记录数
        cursor.execute("""
            SELECT COUNT(*) as total_count 
            FROM user_favorites 
            WHERE user_id = %s
        """, (current_user['user_id'],))
        total_count = cursor.fetchone()['total_count']

        # 计算是否有更多数据
        has_more = (page * page_size) < total_count

        # 返回分页数据
        return {
            "items": favorites,
            "total": total_count,
            "page": page,
            "page_size": page_size,
            "has_more": has_more
        }

    except mysql.connector.Error as e:
        raise HTTPException(status_code=500, detail=f"数据库错误: {e}")
    finally:
        cursor.close()
        connection.close()


@router.get("/favorites/search", response_model=PaginatedFavoriteResponse)
async def search_favorites(
        good_id: Optional[int] = Query(None, description="按商品ID搜索"),
        good_name: Optional[str] = Query(None, description="按商品名称搜索"),
        keyword: Optional[str] = Query(None, description="按关键词搜索（商品名称或描述）"),
        page: int = Query(1, ge=1, description="页码"),
        page_size: int = Query(5, ge=1, le=50, description="每页数量"),
        current_user: dict = Depends(get_current_user)
):
    """搜索用户收藏（分页）- 支持三种搜索方式"""
    connection = get_db_connection()
    if not connection:
        raise HTTPException(status_code=500, detail="数据库连接失败")

    try:
        cursor = connection.cursor(dictionary=True)

        # 计算偏移量
        offset = (page - 1) * page_size

        # 构建基础查询
        base_query = """
            SELECT uf.user_id, uf.good_id, uf.good_name, uf.created_at,
                   g.good_sell_price, g.good_description, g.good_positive_review_rate
            FROM user_favorites uf
            JOIN goods g ON uf.good_id = g.good_id
            WHERE uf.user_id = %s
        """
        query_params = [current_user['user_id']]

        # 构建计数查询
        count_query = """
            SELECT COUNT(*) as total_count
            FROM user_favorites uf
            JOIN goods g ON uf.good_id = g.good_id
            WHERE uf.user_id = %s
        """
        count_params = [current_user['user_id']]

        # 搜索逻辑：优先级从高到低
        search_conditions = []

        # 1. 商品ID精确搜索（最高优先级）
        if good_id is not None:
            search_conditions.append("uf.good_id = %s")
            query_params.append(good_id)
            count_params.append(good_id)
            print(f"🔍 执行商品ID精确搜索: good_id={good_id}")

        # 2. 商品名称精确搜索
        elif good_name:
            search_conditions.append("uf.good_name = %s")
            query_params.append(good_name)
            count_params.append(good_name)
            print(f"🔍 执行商品名称精确搜索: good_name={good_name}")

        # 3. 关键词搜索（商品名称模糊搜索或商品ID搜索）
        elif keyword:
            # 如果关键词是纯数字，优先按商品ID搜索
            if keyword.isdigit():
                search_conditions.append("uf.good_id = %s")
                query_params.append(int(keyword))
                count_params.append(int(keyword))
                print(f"🔍 执行关键词商品ID搜索: keyword={keyword} (识别为商品ID)")
            else:
                # 否则按商品名称模糊搜索
                search_conditions.append("uf.good_name LIKE %s")
                query_params.append(f"%{keyword}%")
                count_params.append(f"%{keyword}%")
                print(f"🔍 执行商品名称模糊搜索: keyword={keyword}")

        # 如果有搜索条件，添加到查询中
        if search_conditions:
            condition_str = " AND " + " AND ".join(search_conditions)
            base_query += condition_str
            count_query += condition_str
        else:
            # 如果没有搜索条件，返回空结果
            print("⚠️ 没有搜索条件，返回空结果")
            return {
                "items": [],
                "total": 0,
                "page": page,
                "page_size": page_size,
                "has_more": False
            }

        # 添加排序和分页
        base_query += " ORDER BY uf.created_at DESC LIMIT %s OFFSET %s"
        query_params.extend([page_size, offset])

        print(f"📊 执行搜索查询: {base_query}")
        print(f"📊 查询参数: {query_params}")

        # 执行查询
        cursor.execute(base_query, query_params)
        favorites = cursor.fetchall()

        # 查询总记录数
        cursor.execute(count_query, count_params)
        total_count_result = cursor.fetchone()
        total_count = total_count_result['total_count'] if total_count_result else 0

        # 计算是否有更多数据
        has_more = (page * page_size) < total_count

        print(f"✅ 搜索结果: 找到 {len(favorites)} 条记录，总计 {total_count} 条")

        # 返回分页数据
        return {
            "items": favorites,
            "total": total_count,
            "page": page,
            "page_size": page_size,
            "has_more": has_more
        }

    except mysql.connector.Error as e:
        print(f"❌ 数据库错误: {e}")
        raise HTTPException(status_code=500, detail=f"数据库错误: {e}")
    finally:
        cursor.close()
        connection.close()


@router.post("/favorites", response_model=SuccessResponse)
async def add_favorite(favorite: FavoriteCreate, current_user: dict = Depends(get_current_user)):
    """添加商品收藏"""
    connection = get_db_connection()
    if not connection:
        raise HTTPException(status_code=500, detail="数据库连接失败")

    try:
        cursor = connection.cursor(dictionary=True)

        # 检查商品是否存在
        cursor.execute("SELECT good_id, good_name FROM goods WHERE good_id = %s", (favorite.good_id,))
        product = cursor.fetchone()
        if not product:
            raise HTTPException(status_code=404, detail="商品不存在")

        # 检查是否已收藏
        cursor.execute(
            "SELECT user_id FROM user_favorites WHERE user_id = %s AND good_id = %s",
            (current_user['user_id'], favorite.good_id)
        )
        if cursor.fetchone():
            raise HTTPException(status_code=400, detail="商品已在收藏夹中")

        # 添加收藏
        cursor.execute(
            "INSERT INTO user_favorites (user_id, good_id, good_name) VALUES (%s, %s, %s)",
            (current_user['user_id'], favorite.good_id, product['good_name'])
        )
        connection.commit()

        return SuccessResponse(
            success=True,
            message="收藏成功",
            data={
                "good_id": favorite.good_id,
                "good_name": product['good_name']
            }
        )

    except mysql.connector.Error as e:
        connection.rollback()
        raise HTTPException(status_code=500, detail=f"数据库错误: {e}")
    finally:
        cursor.close()
        connection.close()


@router.delete("/favorites/{good_id}", response_model=SuccessResponse)
async def remove_favorite(good_id: int, current_user: dict = Depends(get_current_user)):
    """移除商品收藏"""
    connection = get_db_connection()
    if not connection:
        raise HTTPException(status_code=500, detail="数据库连接失败")

    try:
        cursor = connection.cursor()
        cursor.execute(
            "DELETE FROM user_favorites WHERE user_id = %s AND good_id = %s",
            (current_user['user_id'], good_id)
        )
        connection.commit()

        if cursor.rowcount == 0:
            raise HTTPException(status_code=404, detail="收藏记录不存在")

        return SuccessResponse(
            success=True,
            message="取消收藏成功",
            data={"good_id": good_id}
        )

    except mysql.connector.Error as e:
        connection.rollback()
        raise HTTPException(status_code=500, detail=f"数据库错误: {e}")
    finally:
        cursor.close()
        connection.close()
